﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using SweetSQL.Data.Providers;

namespace SweetSQL.Data
{
    /// <summary>
    /// Base class for creating database mapping
    /// </summary>
    /// <typeparam name="T">Your derived type</typeparam>
    public class Database<T> : IDatabase, IDisposable
        where T : Database<T>
    {
        internal IConnectionProvider _connectionProvider = null;
        internal ISyntaxProvider _syntaxProvider = null;

        /// <summary>
        /// Constructor, takes a ConnectionParameters instance
        /// </summary>
        /// <param name="parameters">Settings for building connection string</param>
        protected Database(ConnectionParameters parameters)
        {
            switch (parameters.ConnectionType)
            {
                case ConnectionType.MySql:
                    _connectionProvider = new MySqlConnectionProvider(parameters);
                    break;
                case ConnectionType.OleDb:
                    _connectionProvider = new OleDbConnectionProvider(parameters);
                    break;
                case ConnectionType.Odbc:
                    _connectionProvider = new OdbcConnectionProvider(parameters);
                    break;
                case ConnectionType.SQLite:
                    _connectionProvider = new SQLiteConnectionProvider(parameters);
                    break;
                case ConnectionType.Psql:
                    _connectionProvider = new PsqlConnectionProvider(parameters);
                    break;
                case ConnectionType.SqlServer:
                default:
                    _connectionProvider = new SqlServerConnectionProvider(parameters);
                    break;
            }
            switch (parameters.SyntaxType)
            {
                case SyntaxType.MySql:
                    if (parameters.ConnectionType == ConnectionType.OleDb ||
                        parameters.ConnectionType == ConnectionType.Odbc)
                    {
                        _syntaxProvider = new MySqlOpenSyntaxProvider();
                    }
                    else
                    {
                        _syntaxProvider = new MySqlSyntaxProvider();
                    }
                    break;
                case SyntaxType.Open:
                    _syntaxProvider = new OpenSyntaxProvider();
                    break;
                case SyntaxType.Psql:
                    _syntaxProvider = new PsqlSyntaxProvider();
                    break;
                case SyntaxType.SQLite:
                    _syntaxProvider = new SQLiteSyntaxProvider();
                    break;
                case SyntaxType.SqlServer:
                default:
                    _syntaxProvider = new SqlServerSyntaxProvider();
                    break;
            }
        }

        /// <summary>
        /// Gets a Table instance
        /// </summary>
        /// <typeparam name="TTable">Type of mapped class</typeparam>
        /// <param name="name">Name of table</param>
        /// <returns>New table instance</returns>
        protected Table<TTable> GetTable<TTable>(string name)
            where TTable : class, new()
        {
            return new Table<TTable>
                {
                    Database = this,
                    TableInfo = Reflector<T>.GetTableInfo<TTable>(name),
                };
        }

        /// <summary>
        /// Begins a transaction with the current connection
        /// </summary>
        public void BeginTransaction()
        {
            _connectionProvider.Connect();
            _connectionProvider.BeginTransaction();
        }

        /// <summary>
        /// Commits the current transaction
        /// </summary>
        public void Commit()
        {
            _connectionProvider.Commit();
        }

        /// <summary>
        /// Rolls back the current transaction
        /// </summary>
        public void Rollback()
        {
            _connectionProvider.Rollback();
        }

        #region IDatabase Members

        IEnumerable<TTable> IDatabase.Select<TTable>(Table<TTable> table)
        {
            _connectionProvider.Connect();
            using (IDbCommand command = _connectionProvider.CreateCommand(SqlLanguage.BuildSelect<TTable>(_syntaxProvider, table.TableInfo)))
            {
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return CreateInstance<TTable>(reader, table.TableInfo);
                    }
                }
            }
        }

        TTable IDatabase.Select<TTable>(Table<TTable> table, TTable instance)
        {
            _connectionProvider.Connect();
            using (IDbCommand command = _connectionProvider.CreateCommand(SqlLanguage.BuildSelectOne<TTable>(_syntaxProvider, table.TableInfo)))
            {
                SetParameters(command, instance, table.TableInfo, false, true);

                using (IDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return CreateInstance<TTable>(reader, table.TableInfo);
                    }
                }
            }
            return null;
        }

        int IDatabase.Delete<TTable>(Table<TTable> table)
        {
            _connectionProvider.Connect();
            using (IDbCommand command = _connectionProvider.CreateCommand(SqlLanguage.BuildDelete<TTable>(_syntaxProvider, table.TableInfo)))
            {
                return command.ExecuteNonQuery();
            }
        }

        int IDatabase.Delete<TTable>(Table<TTable> table, TTable instance)
        {
            _connectionProvider.Connect();
            using (IDbCommand command = _connectionProvider.CreateCommand(SqlLanguage.BuildDeleteOne<TTable>(_syntaxProvider, table.TableInfo)))
            {
                SetParameters(command, instance, table.TableInfo, false, true);

                return command.ExecuteNonQuery();
            }
        }

        void IDatabase.Insert<TTable>(Table<TTable> table, TTable instance)
        {
            _connectionProvider.Connect();

            using (IDbCommand command = _connectionProvider.CreateCommand(SqlLanguage.BuildInsert<TTable>(_syntaxProvider, table.TableInfo)))
            {
                SetParameters(command, instance, table.TableInfo, true, false);

                command.ExecuteNonQuery();
            }
        }

        void IDatabase.BulkInsert<TTable>(Table<TTable> table, IEnumerable<TTable> enumerable)
        {
            _connectionProvider.Connect();

            using (IDbCommand command = _connectionProvider.CreateCommand(SqlLanguage.BuildInsert<TTable>(_syntaxProvider, table.TableInfo)))
            {
                foreach (TTable instance in enumerable)
                {
                    SetParameters(command, instance, table.TableInfo, true, false);

                    command.ExecuteNonQuery();
                }
            }
        }

        int IDatabase.Update<TTable>(Table<TTable> table, TTable instance)
        {
            _connectionProvider.Connect();

            using (IDbCommand command = _connectionProvider.CreateCommand(SqlLanguage.BuildUpdate<TTable>(_syntaxProvider, table.TableInfo)))
            {
                SetParameters(command, instance, table.TableInfo, true, true);

                return command.ExecuteNonQuery();
            }
        }

        int IDatabase.Drop<TTable>(Table<TTable> table)
        {
            _connectionProvider.Connect();
            using (IDbCommand command = _connectionProvider.CreateCommand(SqlLanguage.BuildDropTable<TTable>(_syntaxProvider, table.TableInfo)))
            {
                return command.ExecuteNonQuery();
            }
        }

        int IDatabase.Create<TTable>(Table<TTable> table)
        {
            _connectionProvider.Connect();
            using (IDbCommand command = _connectionProvider.CreateCommand(SqlLanguage.BuildCreateTable<TTable>(_syntaxProvider, table.TableInfo)))
            {
                return command.ExecuteNonQuery();
            }
        }

        int IDatabase.ExecuteNonQuery<TTable>(Table<TTable> table, RawQuery rawQuery)
        {
            _connectionProvider.Connect();
            using (IDbCommand command = _connectionProvider.CreateCommand(rawQuery.Sql))
            {
                SetParameters(command, rawQuery);

                return command.ExecuteNonQuery();
            }
        }

        IEnumerable<TTable> IDatabase.ExecuteReader<TTable>(Table<TTable> table, RawQuery rawQuery)
        {
            _connectionProvider.Connect();
            using (IDbCommand command = _connectionProvider.CreateCommand(rawQuery.Sql))
            {
                SetParameters(command, rawQuery);
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return CreateInstance<TTable>(reader, table.TableInfo);
                    }
                }
            }
        }

        #endregion

        internal TTable CreateInstance<TTable>(IDataReader reader, TableInfo tableInfo)
            where TTable : class, new()
        {
            TTable instance = new TTable();
            int length = tableInfo.Columns.Count;
            ColumnInfo column = null;
            for (int i = 0; i < length; i++)
            {
                column = tableInfo.Columns[i];
                column.Set(instance, _connectionProvider.ConvertToClr(column.Type, reader[i]));
            }
            return instance;
        }

        internal void SetParameters(IDbCommand command, RawQuery rawQuery)
        {
            if (rawQuery.Parameters != null)
            {
                IDataParameter parameter = null;
                object value = null;
                Type type = null;
                int length = rawQuery.Parameters.Count;
                for (int i = 0; i < length; i++)
                {
                    value = rawQuery.Parameters[i];
                    type = value == null ? null : value.GetType();
                    parameter = _connectionProvider.CreateParameter(_syntaxProvider.CreateParameterName(i), type, i);
                    parameter.Value = _connectionProvider.ConvertToDatabase(type, value);
                    command.Parameters.Add(parameter);
                }
            }
        }

        internal void SetParameters<TTable>(IDbCommand command, TTable item, TableInfo tableInfo, bool setColumns, bool setWhere)
            where TTable : class, new()
        {
            int length = 0,
                count = 0,
                i = 0;
            ColumnInfo info = null;
            if (command.Parameters.Count == 0)
            {
                IDataParameter parameter = null;
                if (setColumns)
                {
                    length = tableInfo.Columns.Count;
                    for (i = 0; i < length; i++)
                    {
                        info = tableInfo.Columns[i];
                        parameter = _connectionProvider.CreateParameter(_syntaxProvider.CreateParameterName(count), info.Type, count++);
                        parameter.Value = _connectionProvider.ConvertToDatabase(info.Type, info.Get(item));
                        command.Parameters.Add(parameter);
                    }
                }

                if (setWhere)
                {
                    length = tableInfo.PrimaryKeys.Count;
                    for (i = 0; i < length; i++)
                    {
                        info = tableInfo.PrimaryKeys[i];
                        parameter = _connectionProvider.CreateParameter(_syntaxProvider.CreateParameterName(count), info.Type, count++);
                        parameter.Value = _connectionProvider.ConvertToDatabase(info.Type, info.Get(item));
                        command.Parameters.Add(parameter);
                    }
                }
            }
            else
            {
                if (setColumns)
                {
                    length = tableInfo.Columns.Count;
                    for (i = 0; i < length; i++)
                    {
                        info = tableInfo.Columns[i];
                        ((IDataParameter)command.Parameters[count++]).Value = _connectionProvider.ConvertToDatabase(info.Type, info.Get(item));
                    }
                }

                if (setWhere)
                {
                    length = tableInfo.PrimaryKeys.Count;
                    for (i = 0; i < length; i++)
                    {
                        info = tableInfo.PrimaryKeys[i];
                        ((IDataParameter)command.Parameters[count++]).Value = _connectionProvider.ConvertToDatabase(info.Type, info.Get(item));
                    }
                }
            }
        }

        #region IDisposable Members

        /// <summary>
        /// Disposes connection, if one was made
        /// </summary>
        public void Dispose()
        {
            if (_connectionProvider != null)
            {
                _connectionProvider.Dispose();
                _connectionProvider = null;
            }
        }

        #endregion
    }
}
